<?php

namespace app\admin\controller\inventory;

use app\admin\model\information\Led;
use app\common\controller\Backend;
use think\Db;
use think\Exception;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use app\admin\model\inventory\Wmskcview;
/**
 * Wms库存管理管理
 *
 * @icon fa fa-circle-o
 */
class Wmskc extends Backend
{

    /**
     * Kc模型对象
     * @var \app\admin\model\inventory\Wmskc
     */
    protected $model = null;
    protected $modelView = null;
    protected $whTypeArr = [1=>'标准仓','代管仓'];
    public function _initialize()
    {
        parent::_initialize();
        $this->model = new \app\admin\model\inventory\Wmskc;
        $this->modelView = new \app\admin\model\inventory\Wmskcview;
        $this->assign('whTypeArr',$this->whTypeArr);
    }



    /**
     * 默认生成的控制器所继承的父类中有index/add/edit/del/multi五个基础方法、destroy/restore/recyclebin三个回收站方法
     * 因此在当前控制器中可不用编写增删改查的代码,除非需要自己控制这部分逻辑
     * 需要将application/admin/library/traits/Backend.php中对应的方法复制到当前控制器,然后进行修改
     */


    public function index()
    {
        Db::query("SET GLOBAL table_open_cache=16384; ");
        Db::query("SET GLOBAL table_definition_cache=16384;");
        $this->searchFields='inv_name,inv_number,warehouse_name,shelves_name,cBatch';

        //设置过滤方法
        $this->request->filter(['strip_tags', 'trim']);
        if (false === $this->request->isAjax()) {
            return $this->view->fetch();
        }
        //如果发送的来源是 Selectpage，则转发到 Selectpage
        if ($this->request->request('keyField')) {
            return $this->selectpage();
        }
        
        [$where, $sort, $order, $offset, $limit] = $this->buildparams();
        //$list = $this->model->with('shelves')
        $list = $this->modelView
        ->where($where)
        ->where([
            'quantity' => ['GT',0]
        ])
        ->order($sort, $order)
        //->field('fa_wms_kc.*,quantity/fCoefficient store_quantity')
        ->paginate($limit);
        
        $result = ['total' => $list->total(), 'rows' => $list->items()];
        return json($result);
    }
  
    public function import(){
        
        if ($this->request->isPost()) {
            //$params   = $this->request->post("");
            $file = $this->request->request('file');
            if (!$file) {
                $this->error(__('Parameter %s can not be empty', 'file'));
            }
            $filePath = ROOT_PATH . DS . 'public' . DS . $file;
            if (!is_file($filePath)) {
                $this->error(__('No results were found'));
            }
            /* if (empty($params['upload_url'])) {
                $this->error('请选择上传文件');
            } */
            //$inputFileName = ROOT_PATH.'public'.DS.$params['upload_url'];
            $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xls();
            $reader->setReadDataOnly(true);
            $spreadsheet = $reader->load($filePath);//$inputFileName
            //处理Excel文档
            $sheet = $spreadsheet->getSheet(0); // 读取第一個工作表
            $highest_row = $sheet->getHighestRow(); // 取得总行数
            $update_arr = [];//更新
            $add_arr = [];
            $add = 0;
            $kcData = [];
            $order_date = date('Y-m-d',time());
            
            Db::startTrans();
            try{
                for ($row = 2; $row <= $highest_row; $row++) { //行号从1开始
                    $number = trim($sheet->getCell('A' . $row)->getValue());
                    $warehouse_code = trim($sheet->getCell('C' . $row)->getValue());
                    $shelves_code = trim($sheet->getCell('E' . $row)->getValue());
                    //$way = trim($sheet->getCell('H' . $row)->getValue());
                    $way = '增加';
                    $quantity = trim($sheet->getCell('G' . $row)->getValue());
                    $quantity = floatval($quantity);

                    $cBatch = trim($sheet->getCell('H' . $row)->getValue());
                    
                    if(!$number){
                        exception("第{$row}行，物料编码为空");
                    }
                    if(!$warehouse_code){
                        exception("第{$row}行，仓库编码为空");
                    }
                    if(!$shelves_code){
                        exception("第{$row}行，库区、货架编码为空");
                    }
                    if(!$way){
                        exception("第{$row}行，方式为空");
                    }
                    if($way!='增加'&&$way!='减少'){
                        exception("第{$row}行，方式必须是增加、减少");
                    }
                    if($quantity<=0){
                        exception("第{$row}行，库存数量格式错误");
                    }
                    
                    $wmskcInfo = $this->model::getWmskcinfo($number, $warehouse_code, $shelves_code,$cBatch);
                    
                    $inventory = $wmskcInfo[0];
                    if(!$inventory){
                        exception("第{$row}行，物料编码异常");
                    }
                    
                    $warehouse = $wmskcInfo[1];
                    if(!$warehouse){
                        exception("第{$row}行，仓库编码异常");
                    }
                    
                    $shelves = $wmskcInfo[2];
                    if(!$shelves){
                        exception("第{$row}行，库区、货架编码异常");
                    }
                    
                    if(!$inventory['fCoefficient']){
                        exception("第{$row}行，【物料{$inventory['name']}】无换算率");
                    }
                    
                    $kcData = [
                        'code'=>'import_kc',
                        //'factory_code'=>$factory_code,
                        'code_date'=>$order_date,
                        'inv_number'=>$number,
                        'cBatch' => $cBatch,
                        'quantity' => $quantity,
                        'store_quantity' => $quantity/$inventory['fCoefficient'],
                        'warehouse_id'=>$warehouse['id'],
                        'shelves_id'=>$shelves['id']
                    ];

                    if($way=='增加'){
                        $this->model::OperateWmsKc($kcData,'inc',999,'后台导入增库存');
                    }else{
                        $this->model::OperateWmsKc($kcData,'dec',999,'后台导入减库存');
                    }
                    $add++;
                    
                }
                
                Db::commit();
            }catch (\Exception $e){
                Db::rollback();
                trace(['导入库存失败',Db::getLastSql(),$number,$kcData],'error');
                $this->error($e->getMessage());
            }
            //count($add_arr).'条 | 更新'.count($update_arr).'
            $this->success('导入成功,处理'.$add.'条');
        }
        return $this->fetch();
    }
    
    // 导出excel
    public function export()
    {
        // $this->relationSearch = true;
        if ($this->request->isPost()) {
            set_time_limit(0);
            $this->searchFields = 'inv_number,inv_name,shelves_name,shelves_number,wmskc.cBatch';

            $search = $this->request->post('search');
            $ids = $this->request->post('ids');
            $filter = $this->request->post('filter');
            $op = $this->request->post('op');
            $columns = $this->request->post('columns');
            $excel = new Spreadsheet();
            
            $filter = json_decode($this->request->post("filter"), true);
            /* if (isset($filter['consumer.name'])) {
                $consumer_name = $filter['consumer.name'];
                unset($filter['consumer.name']);
                $filter['consumer_name'] = $consumer_name;
                
            } */
            $filter=json_encode($filter);
            $excel->getProperties()
            ->setCreator("WMS")
            ->setLastModifiedBy("WMS")
            ->setTitle("库存明细")
            // ->setSize()
            ->setSubject("Subject");
            $excel->getDefaultStyle()->getFont()->setName('Microsoft Yahei');
            $excel->getDefaultStyle()->getFont()->setSize(12);
            $worksheet = $excel->setActiveSheetIndex(0);
            $worksheet->setTitle('库存明细');
            // $styleArray = array(
            // 'font' => array(
            //     // 'color' => array('rgb' => '000000'),
            //     'size'  => 30,
            //     'name'  => 'Verdana'
            // ));
            // $whereIds = $ids == 'all' ? '1=1' : ['id' => ['in', explode(',', $ids)]];
            $whereIds = $ids == 'all' ? '1=1' : ['wmskc.id' => ['in', $ids]];
            $this->request->get(['search' => $search, 'ids' => $ids, 'filter' => $filter, 'op' => $op]);
            list($where, $sort, $order, $offset, $limit) = $this->buildparams();
            $line = 1;
            $list = [];
            $where2 = [];
            
            $list = $this->model->with('shelves')
            ->where($where)
            ->where([
                'quantity' => ['GT',0]
            ])
            ->where($whereIds)
            ->order($sort, $order)
            ->select();
            
            $new_basic_arr = [];
            $total_num = 0;
            foreach ($list as $keys => $row) {
                $new_basic_arr[$keys]['仓库名称'] = $list[$keys]['warehouse_name'];
                $new_basic_arr[$keys]['仓库编码'] = $list[$keys]['warehouse_number'];
                $new_basic_arr[$keys]['路径名称'] = $list[$keys]['shelves_name'];
                $new_basic_arr[$keys]['路径编码'] = $list[$keys]['shelves_number'];
                $new_basic_arr[$keys]['物料编码'] = $list[$keys]['inv_number'];
                $new_basic_arr[$keys]['物料名称'] = $list[$keys]['inv_name'];
                $new_basic_arr[$keys]['规格'] = $list[$keys]['inv_specification'];
                $new_basic_arr[$keys]['单位'] = $list[$keys]['inv_unit'];
                $new_basic_arr[$keys]['库存数量'] = $list[$keys]['quantity'];
                $new_basic_arr[$keys]['属性'] = $list[$keys]['shelves']['wh_type']==1?'标准仓':'代管仓';
                $new_basic_arr[$keys]['更新时间'] = $list[$keys]['updatetime'];
                $total_num+=$list[$keys]['quantity'];
            }
            
            //20230215合计
            $new_basic_arr[$keys+1][0] = '';
            $new_basic_arr[$keys+1][1] = '';
            $new_basic_arr[$keys+1][2] = '';
            $new_basic_arr[$keys+1][3] = '';
            $new_basic_arr[$keys+1][4] = '';
            $new_basic_arr[$keys+1][5] = '';
            $new_basic_arr[$keys+1][6] = '';
            $new_basic_arr[$keys+1][7] = '合计：';
            $new_basic_arr[$keys+1][8] = round($total_num,3);
            $new_basic_arr[$keys+1][9] = '';
            $new_basic_arr[$keys+1][10] = '';
          
            # 数据填充
            foreach ($new_basic_arr as $key => $v) {
                foreach ($v as $k => $ele) {
                    $tmparray = explode("_text", $k);
                    
                    if (count($tmparray) > 1) {
                        $new_basic_arr[$key][$tmparray[0]] = $ele;
                        unset($new_basic_arr[$key][$k]);
                    }
                }
            }
            
            foreach ($new_basic_arr as $index => $item) {
                $line++;
                $col = 0;
                foreach ($item as $keys => $value) {
                    $worksheet->setCellValueByColumnAndRow($col + 1, $line, $value);
                    $worksheet->getStyleByColumnAndRow($col + 1, $line)->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_TEXT);
                    // $worksheet->getCellByColumnAndRow($col, $line)->getStyle()->applyFromArray($styleArray);
                    $col++;
                }
            }
            // 配置表头
            $first = array_keys($new_basic_arr[0]);
            foreach ($first as $k => $ele) {
                $tmparray = explode("_text", $ele);
                if (count($tmparray) > 1) {
                    unset($first[$k]);
                }
            }
            foreach ($first as $index => $item) {
                $worksheet->setCellValueByColumnAndRow($index + 1, 1, __($item));
                // $worksheet->getColumnDimension($index)->setAutoSize(true);
            }
            // A-Z有用，后面就没用了
            foreach ($worksheet->getColumnIterator() as $column) {
                $worksheet->getColumnDimension($column->getColumnIndex())->setAutoSize(true);
            }
            // 加范围A-B有用，后面就没用了
            // foreach (range('A','B') as $col) {
            //     $worksheet->getColumnDimension($col)->setAutoSize(true);
            // }
            $excel->createSheet();
            // Redirect output to a client’s web browser (Excel2007)
            $title = date("YmdHis");
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            header('Content-Disposition: attachment;filename="' . $title . '.xlsx"');
            header('Cache-Control: max-age=0');
            // If you're serving to IE 9, then the following may be needed
            header('Cache-Control: max-age=1');
            
            // If you're serving to IE over SSL, then the following may be needed
            header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
            header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
            header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
            header('Pragma: public'); // HTTP/1.0
            
            
            $objWriter = IOFactory::createWriter($excel, 'Xlsx');
            $objWriter->save('php://output');
            return;
        }
    }

    /**
     * 删除
     *
     * @param $ids
     * @return void
     * @throws DbException
     * @throws DataNotFoundException
     * @throws ModelNotFoundException
     */
    public function del($ids = null,$mes = false)
    {
        if (false === $this->request->isPost()) {
            $this->error(__("Invalid parameters"));
        }
        $ids = $ids ?: $this->request->post("ids");
        if (empty($ids)) {
            $this->error(__('Parameter %s can not be empty', 'ids'));
        }
        $pk = $this->model->getPk();
        $adminIds = $this->getDataLimitAdminIds();
        if (is_array($adminIds)) {
            $this->model->where($this->dataLimitField, 'in', $adminIds);
        }

        Db::startTrans();
        try{
            $ids = explode(',',$ids);
            foreach($ids as $id){
                $wmskc = $this->model::get($id);
                $kcData = [
                    'code'=>'del_kc',
                    'code_date'=>date('Y-m-d',time()),
                    'inv_number'=>$wmskc->inv_number,
                    'cBatch' => $wmskc->cBatch,
                    'quantity' => $wmskc->quantity,
                    'store_quantity' => $wmskc->store_quantity,
                    'warehouse_id'=>$wmskc->warehouse_id,
                    'shelves_id'=>$wmskc->shelves_id
                ];

                $this->model::OperateWmsKc(
                    $kcData,'dec',998,
                    $this->auth->nickname.'_'.$this->auth->id.'后台删除,减库存'
                );
                $wmskc->delete();
                unset($kcData);
            }
            Db::commit();
        }catch(Exception $e){
            Db::rollback();
            $this->error($e->getMessage());
        }
        $this->success();
    }
}
